﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL {
    public class Flow_Type_DAO {

        /// <summary>
        /// 查找指定条件的流程分类和子流程
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public List<Flow_Type> FindTheFlows(string sql)
        {
            List<Flow_Type> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<Flow_Type>();
                    while (sqlDr.Read())
                    {
                        Flow_Type flow_type = new Flow_Type();
                        flow_type.Id = sqlDr["Id"] == null ? -1 : (int)sqlDr["Id"];
                        flow_type.Sort_id = sqlDr["Flow_Sort"] == null ? -1 : (int)sqlDr["Flow_Sort"];
                        flow_type.Flow_name = sqlDr["Flow_Name"] == null ? null : (string)sqlDr["Flow_Name"];
                        list.Add(flow_type);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 查找指定条件的流程分类和子流程
        /// </summary>
        /// <param name="sqls">需要查询的sql语句键值对</param>
        /// <returns></returns>
        public List<Flow_Type> FindTheFlows(Hashtable sqls) {
            List<Flow_Type> list = null;
            SqlConnection sqlConn = null;
            //SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                if(sqls["sql"] != null) {
                }
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlDr != null) {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }
        /// <summary>
        /// 获取所有分类
        /// </summary>
        /// <returns></returns>
        public List<Flow_Type> FindAllTypes()
        {
            List<Flow_Type> list = new List<Flow_Type>();
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                sc.CommandText = "select * from Flow_Type order by Flow_Sort";
                conn.Open();
                dr = sc.ExecuteReader();
                while (dr.Read())
                {
                    Flow_Type f = new Flow_Type();
                    f.Id = (int)dr["Id"];
                    f.Sort_id = (int)dr["Flow_Sort"];
                    f.Flow_name = (string)dr["Flow_Name"];
                    list.Add(f);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (dr != null)
                {
                    if (!dr.IsClosed)
                    {
                        dr.Close();
                        dr.Dispose();
                    }
                }
                if (conn !=null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return list;
        }
        /// <summary>
        /// 根据分类ID获取分类信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Flow_Type FindByTypeId(int id)
        {
            Flow_Type ft = null;
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                sc.CommandText = "select * from Flow_Type where Id="+id.ToString();
                conn.Open();
                dr = sc.ExecuteReader();
                if(dr.Read())
                {
                    ft = new Flow_Type();
                    ft.Id = (int)dr["Id"];
                    ft.Sort_id = (int)dr["Flow_Sort"];
                    ft.Flow_name = (string)dr["Flow_Name"];
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (dr != null)
                {
                    if (!dr.IsClosed)
                    {
                        dr.Close();
                        dr.Dispose();
                    }
                }
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return ft;
        }
        /// <summary>
        /// 添加分类方法
        /// </summary>
        /// <param name="ft"></param>
        /// <returns></returns>
        public bool AddFlowType(Flow_Type ft)
        {
            bool flag = false;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                conn.Open();
                sc.CommandText = "insert Flow_Type(Flow_Sort,Flow_Name) values(@Flow_Sort,@Flow_Name)";
                sc.Parameters.Add(new SqlParameter("@Flow_Sort", ft.Sort_id));
                sc.Parameters.Add(new SqlParameter("@Flow_Name", ft.Flow_name));
                int i = sc.ExecuteNonQuery();
                if (i > 0)
                {
                    flag = true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return flag;
        }
        /// <summary>
        /// 编辑分类
        /// </summary>
        /// <param name="ft"></param>
        /// <returns></returns>
        public bool UpdateType(Flow_Type ft)
        {
            bool flag = false;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                conn.Open();
                sc.CommandText = "update Flow_Type set Flow_Sort = @Flow_Sort,Flow_Name = @Flow_Name where Id=@Id";
                sc.Parameters.Add(new SqlParameter("@Flow_Sort", ft.Sort_id));
                sc.Parameters.Add(new SqlParameter("@Flow_Name", ft.Flow_name));
                sc.Parameters.Add(new SqlParameter("@Id", ft.Id));
                int i = sc.ExecuteNonQuery();
                if (i > 0)
                {
                    flag = true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return flag;
        }
        /// <summary>
        /// 删除分类
        /// </summary>
        /// <param name="tid"></param>
        /// <returns></returns>
        public bool Deltype(int tid)
        {
            bool flag = false;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                conn.Open();
                sc.CommandText = "delete from Flow_Type where Id=" + tid.ToString();
                int i = sc.ExecuteNonQuery();
                if (i > 0)
                {
                    flag = true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return flag;
        }
    }
}
